import java.sql.*;
import java.util.Scanner;

public class Main {
    static Scanner sc = new Scanner(System.in);

    public static void main(String[] args) {
        while (true) {
            System.out.println("=== 学生信息管理系统 ===");
            System.out.print("用户名: ");
            String username = sc.next();
            System.out.print("密码: ");
            String password = sc.next();

            String role = login(username, password);
            if (role == null) {
                System.out.println("登录失败，请重试！");
                continue;
            }

            switch (role) {
                case "student": studentMenu(username); break;
                case "teacher": teacherMenu(); break;
                case "admin": adminMenu(); break;
            }
        }
    }

    public static String login(String username, String password) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String role = null;

        try {
            conn = DBServer.getConnection();
            String sql = "SELECT role FROM users WHERE username=? AND password=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, username);
            stmt.setString(2, password);
            rs = stmt.executeQuery();

            if (rs.next()) {
                role = rs.getString("role");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBServer.close(conn, stmt, rs);
        }
        return role;
    }

    public static void studentMenu(String username) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBServer.getConnection();
            String sql = "SELECT * FROM students WHERE student_id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, username); // username 是学号字符串
            rs = stmt.executeQuery();

            if (rs.next()) {
                System.out.println("学号: " + rs.getString("student_id"));
                System.out.println("姓名: " + rs.getString("name"));
                System.out.println("Java成绩: " + rs.getInt("java_score"));
                System.out.println("操作系统成绩: " + rs.getInt("os_score"));
            } else {
                System.out.println("学生信息未找到！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBServer.close(conn, stmt, rs);
        }
    }

    public static void teacherMenu() {
        while (true) {
            System.out.println("\n1. 录入成绩  2. 查看成绩  3. 修改成绩  4. 按姓名查询  5. 退出");
            int choice = sc.nextInt();

            switch (choice) {
                case 1: inputScore(); break;
                case 2: viewAllStudents(); break;
                case 3: updateScore(); break;
                case 4: queryByName(); break;
                case 5: return;
            }
        }
    }

    public static void inputScore() {
        System.out.print("学生学号: ");
        String studentId = sc.next();
        System.out.print("姓名: ");
        String name = sc.next();
        System.out.print("Java成绩: ");
        double java = sc.nextDouble();
        System.out.print("操作系统成绩: ");
        double os = sc.nextDouble();

        Connection conn = null;
        PreparedStatement stmtStudent = null;
        PreparedStatement stmtUser = null;

        try {
            conn = DBServer.getConnection();
            conn.setAutoCommit(false); // 事务开始

            // 插入学生表
            String sqlStudent = "INSERT INTO students (student_id, name, java_score, os_score) VALUES (?, ?, ?, ?)";
            stmtStudent = conn.prepareStatement(sqlStudent);
            stmtStudent.setString(1, studentId);
            stmtStudent.setString(2, name);
            stmtStudent.setDouble(3, java);
            stmtStudent.setDouble(4, os);
            int rowsStudent = stmtStudent.executeUpdate();

            // 插入用户表，初始密码用学号或固定密码，这里以学号为密码示例
            String sqlUser = "INSERT INTO users (username, password, role) VALUES (?, ?, 'student')";
            stmtUser = conn.prepareStatement(sqlUser);
            stmtUser.setString(1, studentId);
            stmtUser.setString(2, studentId); // 默认密码同学号，实际可改
            int rowsUser = stmtUser.executeUpdate();

            conn.commit(); // 提交事务

            if (rowsStudent > 0 && rowsUser > 0) {
                System.out.println("学生添加成功，账户已创建，初始密码为学号！");
            } else {
                System.out.println("添加失败！");
            }
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            DBServer.close(null, stmtStudent, null);
            DBServer.close(conn, stmtUser, null);
        }
    }



    public static void viewAllStudents() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBServer.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM students");

            while (rs.next()) {
                System.out.println(rs.getString("student_id") + " " + rs.getString("name") +
                        " Java:" + rs.getInt("java_score") +
                        " OS:" + rs.getInt("os_score"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBServer.close(conn, stmt, rs);
        }
    }

    public static void updateScore() {
        System.out.print("学生学号: ");
        String studentId = sc.next();
        System.out.print("修改Java成绩: ");
        double java = sc.nextDouble();
        System.out.print("修改操作系统成绩: ");
        double os = sc.nextDouble();

        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBServer.getConnection();
            String sql = "UPDATE students SET java_score=?, os_score=? WHERE student_id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setDouble(1, java);
            stmt.setDouble(2, os);
            stmt.setString(3, studentId);

            int rows = stmt.executeUpdate();
            System.out.println(rows > 0 ? "成绩修改成功！" : "修改失败，检查学号。");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBServer.close(conn, stmt, null);
        }
    }

    public static void queryByName() {
        System.out.print("输入姓名查询: ");
        String name = sc.next();

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBServer.getConnection();
            String sql = "SELECT * FROM students WHERE name LIKE ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, "%" + name + "%");
            rs = stmt.executeQuery();

            while (rs.next()) {
                System.out.println(rs.getString("student_id") + " " + rs.getString("name") +
                        " Java:" + rs.getInt("java_score") +
                        " OS:" + rs.getInt("os_score"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBServer.close(conn, stmt, rs);
        }
    }

    public static void adminMenu() {
        while (true) {
            System.out.println("\n1. 添加学生  2. 查看所有学生  3. 查看所有教师  4. 按姓名查询学生  5. 删除学生  6. 删除教师  7. 退出");
            int choice = sc.nextInt();

            switch (choice) {
                case 1: addStudent(); break;
                case 2: viewAllStudents(); break;
                case 3: viewAllTeachers(); break;
                case 4: queryByName(); break;
                case 5: deleteStudent(); break;
                case 6: deleteTeacher(); break;
                case 7: return;
            }
        }

}

    public static void addStudent() {
        System.out.print("学号: ");
        String studentId = sc.next();
        System.out.print("姓名: ");
        String name = sc.next();
        System.out.print("Java成绩: ");
        double javaScore = sc.nextDouble();
        System.out.print("操作系统成绩: ");
        double osScore = sc.nextDouble();

        Connection conn = null;
        PreparedStatement stmtStudent = null;
        PreparedStatement stmtUser = null;

        try {
            conn = DBServer.getConnection();
            conn.setAutoCommit(false); // 事务开始

            // 插入学生表
            String sqlStudent = "INSERT INTO students (student_id, name, java_score, os_score) VALUES (?, ?, ?, ?)";
            stmtStudent = conn.prepareStatement(sqlStudent);
            stmtStudent.setString(1, studentId);
            stmtStudent.setString(2, name);
            stmtStudent.setDouble(3, javaScore);
            stmtStudent.setDouble(4, osScore);
            stmtStudent.executeUpdate();

            // 插入用户表，默认密码设为学号，角色为student
            String sqlUser = "INSERT INTO users (username, password, role) VALUES (?, ?, ?)";
            stmtUser = conn.prepareStatement(sqlUser);
            stmtUser.setString(1, studentId);
            stmtUser.setString(2, studentId); // 默认密码等于学号
            stmtUser.setString(3, "student");
            stmtUser.executeUpdate();

            conn.commit(); // 提交事务
            System.out.println("学生添加成功，默认密码为学号。");

        } catch (SQLException e) {
            try {
                if (conn != null) conn.rollback(); // 事务回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            DBServer.close(null, stmtStudent, null);
            DBServer.close(conn, stmtUser, null);
        }
    }

    public static void deleteStudent() {
        System.out.print("要删除的学生学号: ");
        String studentId = sc.next();

        Connection conn = null;
        PreparedStatement stmt1 = null;
        PreparedStatement stmt2 = null;

        try {
            conn = DBServer.getConnection();
            conn.setAutoCommit(false);

            String sql1 = "DELETE FROM students WHERE student_id = ?";
            stmt1 = conn.prepareStatement(sql1);
            stmt1.setString(1, studentId);
            int rows1 = stmt1.executeUpdate();

            String sql2 = "DELETE FROM users WHERE username = ?";
            stmt2 = conn.prepareStatement(sql2);
            stmt2.setString(1, studentId);
            int rows2 = stmt2.executeUpdate();

            conn.commit();

            if (rows1 > 0 && rows2 > 0) {
                System.out.println("学生及用户账户删除成功！");
            } else {
                System.out.println("删除失败，学生或用户不存在！");
            }
        } catch (SQLException e) {
            try {
                if (conn != null) conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBServer.close(null, stmt1, null);
            DBServer.close(conn, stmt2, null);
        }
    }


    public static void deleteTeacher() {
        System.out.print("要删除的教师工号: ");
        String teacherId = sc.next();

        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBServer.getConnection();
            String sql = "DELETE FROM teachers WHERE teacher_id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, teacherId);

            int rows = stmt.executeUpdate();
            System.out.println(rows > 0 ? "教师删除成功！" : "删除失败，工号不存在。");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBServer.close(conn, stmt, null);
        }
    }

    public static void viewAllTeachers() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBServer.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM teachers");

            while (rs.next()) {
                System.out.println(rs.getString("teacher_id") + " " + rs.getString("name"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBServer.close(conn, stmt, rs);
        }
    }
}

